
import 'dart:developer';
import 'dart:io';

import 'package:DiamondDiary/database/note_info.dart';
import 'package:DiamondDiary/database/tag_info.dart';
import 'package:DiamondDiary/database/user_info.dart';
import 'package:path/path.dart' ;
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';

class DBHelper{


  Future<Database> _getDataBase() async {
    var version = 6;
    var create =  (db, version) async {
      await db.execute(UserInfo.CREATE);
      await db.execute(NoteInfo.CREATE);
      await db.execute(TagInfo.CREATE);
    };
    var update = (db, oldVersion, newVersion) async {
      // await db.execute('ALTER TABLE user ADD authentication TEXT');
      if(oldVersion == 1 && newVersion == 2){
        await db.execute(TagInfo.CREATE);
      }
      if(oldVersion<3){
        await db.execute('DROP TABLE ${UserInfo.NAME}');
        await db.execute(UserInfo.CREATE);
      }
      if(oldVersion<4){
        await db.execute('ALTER TABLE ${NoteInfo.NAME} ADD COLUMN userId TEXT');
        await db.execute('ALTER TABLE ${TagInfo.NAME} ADD COLUMN refCount INTEGER');
      }
      if(oldVersion == 5){
        await db.execute('DROP TABLE ${TagInfo.NAME}');
        await db.execute(TagInfo.CREATE);
      }
    };
    if (Platform.isWindows || Platform.isLinux) {
      final scriptDir = File(Platform.script.toFilePath()).parent;
      final libraryNextToScript = File(join('${scriptDir.path}','diamond.db'));
      // var path = inMemoryDatabasePath;
      var path = libraryNextToScript.path;
      print("path:$path");
      return await databaseFactoryFfi.openDatabase(path,
          options:OpenDatabaseOptions(
            version: version,
            onCreate: create,
            onUpgrade: update,
            singleInstance: true,
          )
      );
    }else {
      var path = await getDatabasesPath();
      return await openDatabase(
        join(path, 'diamond.db'),
        version: version,
        onCreate: create,
        onUpgrade: update,
        singleInstance: true,
      );
    }
  }

  Future<void> updateUser(UserInfo info) async{
    final Database db = await _getDataBase();
    var list = await db.query(UserInfo.NAME);
    if(list.isNotEmpty){
      await db.update(
          UserInfo.NAME, info.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
    }else {
      await db.insert(
          UserInfo.NAME, info.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
    }
    db.close();
  }

  Future<UserInfo> getUser() async{
    final Database db = await _getDataBase();
    var list = await db.query(UserInfo.NAME);
    db.close();
    if(list.isNotEmpty){
      return UserInfo.fromJson(list[0]);
    }
    return null;
  }

  Future<void> saveNote(NoteInfo info) async{
    final Database db = await _getDataBase();
    await db.insert(
        NoteInfo.NAME, info.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
    db.close();
  }

  Future<void> modifyNote(NoteInfo info) async{
    final Database db = await _getDataBase();
    await db.update(
        NoteInfo.NAME, info.toJson(), conflictAlgorithm: ConflictAlgorithm.replace);
    db.close();
  }

//按时间流-按日期
  Future<List<Map<String,dynamic>>> getNoteInfo({
    String where,
    List<Object> hereArgs,
    int limit,
    int offset,
  }) async {
    final Database db = await _getDataBase();
    var list = await db.query(NoteInfo.NAME,limit: limit,offset: offset,orderBy:'commitTime');
    db.close();
    return list;
  }

  Future<List<Map<String,dynamic>>> getTagInfo({
    String where,
    List<Object> hereArgs,
    int limit,
    int offset,
    String orderBy,
  }) async {
    final Database db = await _getDataBase();
    var list = await db.query(TagInfo.NAME,limit: limit,offset: offset,orderBy:orderBy??'createTime');
    db.close();
    return list;
  }

  Future<int> saveTag(TagInfo info) async{
    final Database db = await _getDataBase();
    int count = await db.insert(
        TagInfo.NAME, info.toJson(), conflictAlgorithm: ConflictAlgorithm.ignore);
    db.close();
    return count;
  }

  Future<int> updateTag(TagInfo info) async{
    final Database db = await _getDataBase();
    int count = await db.update(
        TagInfo.NAME, info.toJson(), conflictAlgorithm: ConflictAlgorithm.ignore);
    db.close();
    return count;
  }

  Future<int> removeTag(String tag) async{
    final Database db = await _getDataBase();
    int cnt = await db.delete(
        TagInfo.NAME,where: 'tag = ?', whereArgs: [tag]);
    db.close();
    return cnt;
  }

}